﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess;
using SubSonic;


namespace Business
{
    public class ProjectDetails
    {
        public static List<GridContractType> GetProjectByUserId(Guid userId)
        {
            TblContractCollection contracts = new TblContractCollection();
            contracts.LoadAndCloseReader(TblContract.FetchByParameter(TblContract.Columns.UserId, Comparison.Equals, userId));
            return GridDataRetrival(contracts);
        }

        public static List<GridContractType> GetProjectByUserIdPaged(int pageSize, int pageIndex, Guid userId)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            q.AddWhere(TblContract.Columns.UserId , Comparison.Equals, userId);
            q.PageSize = pageSize;
            q.PageIndex = pageIndex + 1;
            contracts.LoadAndCloseReader(q.ExecuteReader());
            return GridDataRetrival(contracts, true);
        }

        public static List<GridContractType> GetProjectByFourthFactorUserId(object fuserId)
        {
            TblContractCollection contracts = new TblContractCollection();
            contracts.LoadAndCloseReader(TblContract.FetchByParameter(TblContract.Columns.FourthFactorUserId, Comparison.Equals, fuserId));
            return GridDataRetrival(contracts);
        }
        public static List<GridContractType> GetProjectByFourthFactorUserIdandrole(object fuserId,bool searchAdvisor,bool searchSurveyor)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            q.AddWhere(TblContract.Columns.FourthFactorUserId, Comparison.Equals, fuserId);
            if ((searchSurveyor == true) && (searchAdvisor == false))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
            }
            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
            }
            contracts.LoadAndCloseReader(q.ExecuteReader());
            return GridDataRetrival(contracts, true);
        }
        public static List<GridContractType> SelectProjectsByCity(Guid cityId)
        {
            List<GridContractType> output = new List<GridContractType>();
            TblBakhshCollection bakhshCollection = new TblBakhshCollection();
            TblDehestanCollection dehestanCollection = new TblDehestanCollection();
            TblVillageCollection villageCollection = new TblVillageCollection();
            TblContractCollection contractCollection = new TblContractCollection();
            bakhshCollection.LoadAndCloseReader(TblBakhsh.FetchByParameter(TblBakhsh.Columns.CityId, Comparison.Equals,
                                                                           cityId));
            foreach (var baksh in bakhshCollection)
            {
                dehestanCollection.LoadAndCloseReader(TblDehestan.FetchByParameter(TblDehestan.Columns.BakhshId,
                                                                                   Comparison.Equals, baksh.Id));
                foreach (var dehestan in dehestanCollection)
                {
                    villageCollection.LoadAndCloseReader(TblVillage.FetchByParameter(TblVillage.Columns.DehstanId,
                                                                                     Comparison.Equals, dehestan.Id));
                    foreach (var village in villageCollection)
                    {
                        contractCollection.LoadAndCloseReader(TblContract.FetchByParameter(
                            TblContract.Columns.VillageId, Comparison.Equals,
                            village.Id));
                    }
                    villageCollection.Clear();

                }
                dehestanCollection.Clear();
            }
            
               return( GridDataRetrival(contractCollection,true));
                
           
            //return output;
        }


        public static List<GridContractType> GetProjectByUserIdVillageId(Guid userId, Guid villageId)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            q.AddWhere(TblContract.Columns.UserId, userId);
            q.AddWhere(TblContract.Columns.VillageId, villageId);
            q.Execute();
            contracts.LoadAndCloseReader(TblContract.FetchByQuery(q));
            return GridDataRetrival(contracts);
        }

        public static List<GridContractType> GetProjectVillageIdVillageName(Guid villageId, bool searchAdvisor, bool searchSurveyor)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query( TblContract.Schema );
            if ( (searchSurveyor == true) && (searchAdvisor==false) )
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
                q.AddWhere(TblContract.Columns.VillageId, Comparison.Equals, villageId);
            }
            if ((searchSurveyor == true) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.VillageId, Comparison.Equals, villageId);
            }
            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
                q.AddWhere(TblContract.Columns.VillageId, Comparison.Equals, villageId);
            }
            contracts.LoadAndCloseReader( q.ExecuteReader());
            return GridDataRetrival(contracts);
        }

        public static List<GridContractType> GetProjectAllPaged(int pageSize, int pageIndex, bool searchAdvisor, bool searchSurveyor)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query( TblContract.Schema );
            if ((searchSurveyor == true) && (searchAdvisor == false))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
            }
            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
            }

            if ((searchSurveyor == true) && (searchAdvisor == true))
            {
                q.OR(TblContract.Columns.ContractType, Comparison.Equals, true);
                q.OR(TblContract.Columns.ContractType, Comparison.Equals, false);
            }

            if ((searchSurveyor == false) && (searchAdvisor == false))
            {
                q.AND(TblContract.Columns.ContractType, Comparison.Equals, true);
                q.AND(TblContract.Columns.ContractType, Comparison.Equals, false);
            }
            q.PageSize = pageSize;
            q.PageIndex = pageIndex+1;
            contracts.LoadAndCloseReader(q.ExecuteReader());
            return GridDataRetrival(contracts , true);
        }
        public static List<GridContractType> GetProjectAllPagedFourthFactor(object fuserid, int pageSize, int pageIndex, bool searchAdvisor, bool searchSurveyor)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            q.AddWhere(TblContract.Columns.FourthFactorUserId,fuserid);
            if ((searchSurveyor == true) && (searchAdvisor == false))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
            }
            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
            }

            if ((searchSurveyor == true) && (searchAdvisor == true))
            {
                q.OR(TblContract.Columns.ContractType, Comparison.Equals, true);
                q.OR(TblContract.Columns.ContractType, Comparison.Equals, false);
            }

            if ((searchSurveyor == false) && (searchAdvisor == false))
            {
                q.AND(TblContract.Columns.ContractType, Comparison.Equals, true);
                q.AND(TblContract.Columns.ContractType, Comparison.Equals, false);
            }

            q.PageSize = pageSize;
            q.PageIndex = pageIndex + 1;
            contracts.LoadAndCloseReader(q.ExecuteReader());
            return GridDataRetrival(contracts, true);
        }
        public static List<GridContractType> GetProjectAllPagedshahrestan(object cityid, int pageSize, int pageIndex, bool searchAdvisor, bool searchSurveyor)
        {
            List<GridContractType> output = new List<GridContractType>();
            TblBakhshCollection bakhshCollection = new TblBakhshCollection();
            TblDehestanCollection dehestanCollection = new TblDehestanCollection();
            TblVillageCollection villageCollection = new TblVillageCollection();
            TblContractCollection contractCollection = new TblContractCollection();
            bakhshCollection.LoadAndCloseReader(TblBakhsh.FetchByParameter(TblBakhsh.Columns.CityId, Comparison.Equals,
                                                                           cityid));
            foreach (var baksh in bakhshCollection)
            {
                dehestanCollection.LoadAndCloseReader(TblDehestan.FetchByParameter(TblDehestan.Columns.BakhshId,
                                                                                   Comparison.Equals, baksh.Id));
                foreach (var dehestan in dehestanCollection)
                {
                    villageCollection.LoadAndCloseReader(TblVillage.FetchByParameter(TblVillage.Columns.DehstanId,
                                                                                     Comparison.Equals, dehestan.Id));
                    foreach (var village in villageCollection)
                    {
                        contractCollection.LoadAndCloseReader(TblContract.FetchByParameter(
                            TblContract.Columns.VillageId, Comparison.Equals,
                            village.Id));
                    }
                    villageCollection.Clear();

                }
                dehestanCollection.Clear();
            }

            return (GridDataRetrival(contractCollection, true));
                
           
        }
        public static List<GridContractType> GetProjectAll(bool searchAdvisor, bool searchSurveyor)
        {
            TblContractCollection contracts = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            if ((searchSurveyor == true) && (searchAdvisor == false))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
            }
            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
            }
            contracts.LoadAndCloseReader(q.ExecuteReader());
            return GridDataRetrival(contracts, true);
        }

        public static List<GridContractType> GridDataRetrival(TblContractCollection contracts)
        {
            List<GridContractType> outputList = new List< GridContractType >();
            foreach ( var contract in contracts )
            {
                outputList.Add( new GridContractType( contract.VillageId, contract.UserId ,Convert.ToDateTime( contract.ContractDate ) , contract.ContractNumber , contract.Id, false, Convert.ToBoolean( contract.ContractType))); 
            }
            return outputList;
        }


        public static List<GridContractType> GridDataRetrival(TblContractCollection contracts, bool needDetails)
        {
            List<GridContractType> outputList = new List<GridContractType>();
            foreach (var contract in contracts)
            {
                outputList.Add(new GridContractType(contract.VillageId, contract.UserId, Convert.ToDateTime(contract.ContractDate), contract.ContractNumber, contract.Id, true, Convert.ToBoolean(contract.ContractType)));
            }
            return outputList;
        }

        public static int GetTotalPageCountOfContracts(int pageSize, bool searchAdvisor=true, bool searchSurveyor=true)
        {
            double temp =Convert.ToDouble(GetTotalRecordCountOfContracts( searchAdvisor , searchSurveyor)) / Convert.ToDouble(pageSize);
            if(temp == Math.Ceiling( temp ))
            {
                return Convert.ToInt32( temp);
            }
            else
            {
                return Convert.ToInt32(Math.Ceiling(temp ) );
            }
        }

        public static int GetTotalRecordCountOfContracts(bool searchAdvisor = true, bool searchSurveyor = true)
        {
            TblContractCollection contractCollection = new TblContractCollection();
            Query q = new Query(TblContract.Schema);
            if ((searchSurveyor == true) && (searchAdvisor == false))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, false);
            }

            if ((searchSurveyor == false) && (searchAdvisor == true))
            {
                q.AddWhere(TblContract.Columns.ContractType, Comparison.Equals, true);
            }
            q.GetCount(TblContract.Columns.Id);
            contractCollection.LoadAndCloseReader(q.ExecuteReader());
            return contractCollection.Count;
        }
    }
}